﻿<?php
/* ******************************
 * Overhead Out aJax actions
 * ******************************
 */
 
include('../../includes/classes/core.php');
$action 	= $_REQUEST['act'];
$error		= '';
$data		= '';
$user_id	= $_SESSION['USERID'];

switch ($action) {
    case 'get_add_page':
	    $o_type		= $_REQUEST['ot'];
		$page		= GetPage('', $o_type);
		$data		= array('page' => $page);
		
        break;
    case 'get_edit_page':
	    $overhead_id= $_REQUEST['id'];
		$page		= GetPage(GetOverhead($overhead_id));
        
        $data 		= array('page'	=> $page);
        
        break;
    case 'get_list':
	    $count	= $_REQUEST['count'];
	    $hidden = $_REQUEST['hidden'];
	    $type	= $_REQUEST['type'];
	    
	    if($type == 0){ //Out
		    $rResult = mysql_query("SELECT `O`.`id` AS `id`,
										   `O`.`waybill_number`,
										   `OT`.`name` AS `type`,
										   IFNULL(CONCAT('(',`O`.`buyer_tin`,') ', `O`.`buyer_name`),'()') AS `partner`,
										   `O`.`full_amount`,
										   `O`.`activate_date`
									FROM `overhead` AS `O` LEFT JOIN `overhead_types` AS `OT`
										ON `O`.`type` = `OT`.`id`
									WHERE `O`.`stream` = $type");
		}else{ //In
	    	$overhead_id = $_REQUEST['o_id'];
	    	if (empty($overhead_id)) {
			    $rResult = mysql_query("SELECT `O`.`id` AS `id`,
											   `O`.`waybill_number`,
											   `OT`.`name` AS `type`,
											   IFNULL(CONCAT('(',`O`.`seller_tin`,') ', `O`.`seller_name`),'()') AS `partner`,
											   `O`.`full_amount`,
											   `O`.`activate_date`,
											   `O`.`is_confirmed` as `is_confirmed`
										FROM `overhead` AS `O` LEFT JOIN `overhead_types` AS `OT`
											ON `O`.`type` = `OT`.`id`
										WHERE `O`.`stream` = $type");
	    	}else{
			    $rResult = mysql_query("SELECT `O`.`id` AS `id`,
											   `O`.`waybill_number`,
											   `OT`.`name` AS `type`,
											   IFNULL(CONCAT('(',`O`.`seller_tin`,') ', `O`.`seller_name`),'()') AS `partner`,
											   `O`.`full_amount`,
											   `O`.`activate_date`
										FROM `overhead` AS `O` LEFT JOIN `overhead_types` AS `OT`
											ON `O`.`type` = `OT`.`id`
										WHERE `O`.`stream` = $type && `O`.`id` = $overhead_id");
	    		
	    	}
		}
		$data = array(
			"aaData"	=> array()
		);
		
		if(!$rResult){
		    $error = 'Invalid query: ' . mysql_error();
		}else{
			while ( $aRow = mysql_fetch_array( $rResult ) )
			{
				$row = array();
				for ( $i = 0 ; $i < $count ; $i++ )
				{
					/* General output */
					if (empty($overhead_id) && $aRow['is_confirmed']  != '2' && $type != 0){
						$row[] = '<p style=" font-weight: bold; "><b>'. $aRow[$i].'</b></p>';
					}else{
						$row[] = $aRow[$i];
					}
					if($i == ($count - 1)){
						$row[] = '<input type="checkbox" name="check_' . $aRow[$hidden] . '" class="check" value="' . $aRow[$hidden] . '" />';
					}
				}
				$data['aaData'][] = $row;
			}		
		}
		
        break;
    case 'save_overhead':
		$overhead_id 	= $_REQUEST['id'];

		$arr = array(
				//Object ID
				"o_obj"				=> $_REQUEST['obj'],
				
				"o_type"			=> $_REQUEST['ot'],
				"o_number"			=> $_REQUEST['on'],
				"actation_data"		=> $_REQUEST['ad'],
				"local_par_id"		=> $_REQUEST['pi'],
				
				//Transport
				"transport_type"	=> $_REQUEST['tt'],
				"driver_id"			=> $_REQUEST['di'],
				"driver_name"		=> $_REQUEST['dn'],
				"auto_number"		=> $_REQUEST['an'],
				"auto_trailer"		=> $_REQUEST['at'],
				"auto_expence_n"	=> $_REQUEST['aen'],
				"auto_expence"		=> $_REQUEST['ae'],
				
				//Buyer
				"buyer_tin"			=> $_REQUEST['bin'],
				"buyer_name"		=> $_REQUEST['bn'],
				"buyer_addree"		=> $_REQUEST['ba']				
		);
		
		if(empty($overhead_id)){
			$id		= AddOverhead($user_id, $arr);
			$data	= array('overhead_id' => $id);
		}else{
			$data	= array('overhead_id' => $overhead_id);
		}
		
        break;
    case 'disable':
		$overhead_id 	= $_REQUEST['id'];
		DeleteOverhead($overhead_id);
		
        break;
	case 'add_reserve':
		$overhead_id 	= $_REQUEST['id'];
		$object 		= $_REQUEST['obj'];
		AddReserve($overhead_id, $object);
		
        break;
	case 'add_partner_acc':
		$overhead_id 	= $_REQUEST['id'];
		$partner_id 	= GetPartnerID($_REQUEST['bin']);

		$arr = array(
				"method"			=> $_REQUEST['pm'],		
				"nided"				=> $_REQUEST['pn'],
				"payed"				=> $_REQUEST['p'],
				"needed_date"		=> $_REQUEST['pnd'],
				"type"				=> $_REQUEST['type']
		);
		AddPartnerAcc($overhead_id, $partner_id, $arr);
		
        break;
	case 'check_identity':
		$overhead_id 	= $_REQUEST['id'];
		$check			= ChackGoodsIdentity($overhead_id);
		if(!$check){
			$error = '1 - მოახდინეთ ყველა პროდუქტის იდენტიფიკაცია!';
		}else{
			$check			= ChackOverheadIdentity($overhead_id);			
			if(!$check){
				$error = '2 - მოახდინეთ ყველა პროდუქტის იდენტიფიკაცია!';
			}
		}
		
        break;
	case 'check_partner':
		$partner_id 	= $_REQUEST['id'];
		$check			= ChackPartnerExist($partner_id);
		if($check){
			$error = 'მოახდინეთ პარტნიორის იდენტიფიკაცია!';
		}
		
        break;
    case 'get_local_id':
		$local_id = GetLocalID();
		
		$data = array('local_id' => $local_id);
        
        break;
    case 'get_current_object':
    	$overhead_id	= $_REQUEST['id'];
		
    	$object_id = GetOverheadObject($overhead_id);
		$data = array('object_id' => $object_id);
        
        break;
    case 'get_buyer':
		$tin	 		= $_REQUEST['tn'];
		$name 			= htmlspecialchars($_REQUEST['nm']);	
		$res 			= GetBuyer($tin, $name);			
		if(!$res){
			$error = 'პარტნიორი ვერ მოიძებნა!';
		}else{
			$data = array('tin' => $res['tin'],
						  'name' => $res['name'],
						  'address' => $res['legal_address']);
		}
		
        break;
    case 'get_driver':
		$tin	 		= $_REQUEST['tn'];
		$name 			= htmlspecialchars($_REQUEST['nm'], ENT_QUOTES);
		$res 			= GetDriver($tin, $name);			
		if(!$res){
			$error = 'მძღოლი ვერ მოიძებნა!';
		}else{
			$data = array('tin' => $res['tin'],
						  'name' => $res['name']);
		}
		
        break;
    case 'get_rs_id':
    	$overhead_id	= $_REQUEST['id'];
		$rs_id			= GetRSID($overhead_id);
		
		$data = array('rs_id' => $rs_id);
        
        break;
    case 'get_parent_id':
    	$overhead_id	= $_REQUEST['id'];
		$local_par_id	= GetParentID($overhead_id);
		
		$data = array('local_par_id' => $local_par_id);
        
        break;
    case 'set_status':
		$overhead_id 	= $_REQUEST['o_id'];
		$status			= $_REQUEST['os'];
		SetStatus($overhead_id, $status);
		
        break;
    case 'clear_db':
		ClearDB();
		
        break;
    default:
       $error = "Action is Null";
}

$data['error'] = $error;

echo json_encode($data);


/* ******************************
 *	Overhead Out Functions
 * ******************************
 */

function ClearDB()
{
    $req = mysql_query("SELECT `id`
						FROM `overhead_detail`
						WHERE `overhead_id` NOT IN (SELECT `id` FROM `overhead`)");
		
	while( $res = mysql_fetch_assoc($req)){
		mysql_query("DELETE
					 FROM
						`overhead_detail`
					 WHERE
						`id` = $res[id]");
	}
}

function AddOverhead($user_id, $arr)
{
	global $error;
	$prop			= GetCompanyProperties();
	$object_id		= $arr['o_obj'];
	$car_object_id	= $arr['auto_number'];
	$type			= $arr['o_type'];
	
	$result = mysql_query("INSERT INTO `overhead`
							(`user_id`, `type`, `waybill_number`, `activate_date`, `local_par_id`) 
						   VALUES
							($user_id, $type, '$arr[o_number]', '$arr[actation_data]', '$arr[local_par_id]')");
	
	$id				= mysql_insert_id();
	$full_amount	= GetFullAmount($id);
	$begin_date		= date("Y-m-d H:i:s");
	
	if (!$result) {
		$error = 'Invalid query: ' . mysql_error() . '\n';
	}else{		
		//Transport
		if($type != 3 && $type != 6){//3 is Without Transportation, 6 is Sub Overhead
			$result = mysql_query("UPDATE
					    				`overhead`
								   SET
									    `user_id`			= $user_id,
									    `trans_id`			= $arr[transport_type],
									    `driver_tin`		= '$arr[driver_id]',
									    `driver_name`		= '$arr[driver_name]',
									    `car_number`		= $car_object_id,
									    `trans_txt`			= '$arr[auto_trailer]',
									    `transport_coast`	= '$arr[auto_expence_n]',
									    `tran_cost_payer`	= $arr[auto_expence]
								   WHERE
										`id` = $id");
			
			if (!$result) {
				$error .= 'Invalid query: ' . mysql_error() . '\n';
			}
		}
		
		
		//Saller
		$result = mysql_query("UPDATE
					    			`overhead`
							   SET
									`user_id`		= $user_id,
								    `seller_tin`	= '$prop[tin]',
								    `seller_name`	= '$prop[name]',
								    `start_address`	= '$prop[address]',
								    `seller_un_id`	= '$prop[un_id]',
								    `s_user_id`		= '$prop[s_user_id]',
								    `full_amount`	= '$full_amount',
								    `begin_date`	= '$begin_date'
							   WHERE
									`id` = $id");
		if (!$result) {
			$error .= 'Invalid query: ' . mysql_error() . '\n';
		}
		
		//Buyer
		if($type != 1 && $type != 4){ //1 is Domestic Traffic, 4 is Distribution
			$result = mysql_query("UPDATE
						    			`overhead`
								   SET
					
									    `user_id`		= $user_id,
									    `buyer_tin`		= '$arr[buyer_tin]',
									    `buyer_name`	= '$arr[buyer_name]',
									    `end_address`	= '$arr[buyer_addree]'
								   WHERE
										`id` = $id");
			
			if (!$result) {
				$error .= 'Invalid query: ' . mysql_error() . '\n';
			}
		}	
	
		//Sub Overhead
		if($type == 6){ //6 is Sub Overhead
			$rs_id	= GetRSID($arr['local_par_id']);
			$result = mysql_query("UPDATE
						    			`overhead`
								   SET
									    `user_id`		= $user_id,
									    `par_id`		= '$rs_id'
								   WHERE
										`id` = $id");
		
		    $req = mysql_query("SELECT `goods_id`, `quantity_f`
								FROM `overhead_detail`
								WHERE `overhead_id` = $id");
				
			while( $res = mysql_fetch_assoc($req)){			
				$result = mysql_query("UPDATE
							    			`overhead_detail`
									   SET
									    	`user_id`		= $user_id,
										    `quantity`		= $res[quantity_f],
										    `quantity_f`	= $res[quantity_f]
									   WHERE
											`overhead_id` = '$arr[local_par_id]' && `goods_id` = $res[goods_id]");
			}
			
			if (!$result) {
				$error .= 'Invalid query: ' . mysql_error() . '\n';
			}
		}
		
		//Calculate on reserves
		if($type == 4){ //4 is Distribution
			$req = mysql_query("SELECT `goods_id`,
									   `quantity`
								FROM `overhead_detail`
								WHERE `overhead_id` = $id");
				
			while( $res = mysql_fetch_assoc($req)){
				$self_cost	= GetPrice($res['goods_id']);
				$c_date		= date('Y-m-d H:i:s');
				
				mysql_query("INSERT INTO `reserve`
								(`user_id`, `object_id`, `overhead_id`, `production_id`, `quantity`, `price`, `create_date`)
							 VALUES
								($user_id, $car_object_id, $id, $res[goods_id], $res[quantity], $self_cost, '$c_date')");	
				
				OverheadTakeOff($res['goods_id'], $res['quantity'], $object_id);
			}		
		}else if($type == 6){ //6 is Sub Overhead
			$car_object_id = GetCarNumber($arr['local_par_id']);
			
			$req = mysql_query("SELECT `goods_id`,
									   `quantity`
								FROM `overhead_detail`
								WHERE `overhead_id` = $id");

			mysql_query("INSERT INTO `sale_transaction`
							(`user_id`, `object_id`, `start_date`, `status`)
						 VALUES
							($user_id, $car_object_id, '$begin_date', 5)");
			
			$transaction_id = mysql_insert_id();
			while( $res = mysql_fetch_assoc($req)){
				OverheadTakeOff($res['goods_id'], $res['quantity'], $car_object_id);								
				OverheadSale($transaction_id, $res['goods_id'], $res['quantity']);				
			}
			
			$sum_cost		= GetSumPrice($car_object_id);
			$sum_self_cost	= GetSumSelfPrice($car_object_id);
			
			OverheadSaleDone($car_object_id, $sum_cost, $sum_self_cost);
		}
		
		return $id;
	}
}

function OverheadTakeOff($prod_id, $quantity, $object)
{
	$req_quantity = $quantity;
	$res = mysql_query("SELECT  `reserve`.`id`,
								`reserve`.`production_id`,
								`reserve`.`object_id`,
								`reserve`.`sold`,
								`reserve`.`quantity` - `reserve`.`sold` AS `diff`,
								`reserve`.`create_date`
						FROM `reserve`
						WHERE `reserve`.`object_id` = $object && `reserve`.`production_id` = $prod_id
						ORDER BY `reserve`.`create_date` ASC" );
	
	while ($row = mysql_fetch_array($res)) {
		if ($req_quantity != 0) {
			if($row['diff'] > $req_quantity){
				$sum = $row['sold'] + $req_quantity;
				mysql_query("UPDATE `reserve`
							 SET	`sold` = $sum
							 WHERE	`id` = $row[id]" );
				$req_quantity = 0;
			}else if($row['diff'] == $req_quantity){
				$sum = $row['sold'] + $row['diff'];
				mysql_query("UPDATE `reserve`
							 SET	`sold` = $sum
							 WHERE	`id` = $row[id]" );
				$req_quantity = 0;
			}else{
				$sum = $row['sold'] + $row['diff'];
				mysql_query("UPDATE `reserve`
							 SET	`sold` = $sum
							 WHERE	`id` = $row[id]" );
				$req_quantity = $req_quantity - $row['diff'];
			}
		}
	}
}

function OverheadSale($transaction_id, $prod_id, $quantity)
{
	$self_cost	= GetPrice($prod_id);
	$cost		= GetPricelistPrice($prod_id);
	
	mysql_query("INSERT INTO `sale_transaction_detail`
					(`transaction_id`, `production_id`, `product_quantity`, `self_cost`, `cost`)
				 VALUES
					($transaction_id, $prod_id, $quantity, $self_cost, $cost)");	
	
}

function OverheadSaleDone($object_id, $sum_cost, $sum_self_cost)
{
	$end_date = date('Y-m-d H:i:s');

	mysql_query("UPDATE
					`sale_transaction`
				 SET
					`end_date`			=  '$end_date',
					`sum_self_cost`		=  $sum_self_cost,
					`sum_cost`          =  $sum_cost,
					`status`			=  6
				 WHERE `object_id` = $object_id && `status` = 5");
}

function GetPrice($production_id)
{
	$res = mysql_query("SELECT `price`
						FROM `reserve`
						WHERE `production_id` = $production_id
						GROUP BY `production_id`");
	
	$row = mysql_fetch_assoc($res);
	return $row['price'];
}

function GetPricelistPrice($production_id)
{
	$res = mysql_query("SELECT `price`
						FROM `pricelist`
						WHERE `production_id` = $production_id");

	$row = mysql_fetch_assoc($res);
	return $row['price'];
}

function GetSelfPrice($production_id)
{
	$res = mysql_query("SELECT  SUM(`reserve`.`price` * (`reserve`.`quantity` - `reserve`.`sold`)) / SUM(`reserve`.`quantity`  -  `reserve`.`sold`) AS `self_cost`
						FROM `reserve`
						WHERE `reserve`.`production_id` = $production_id
						GROUP BY `reserve`.`production_id`");
	
	$row = mysql_fetch_assoc($res);
	return $row['self_cost'];
}

function GetSumPrice($object_id)
{
	$res = mysql_query("SELECT ROUND(SUM(`sale_transaction_detail`.`cost` * `sale_transaction_detail`.`product_quantity`), 2 ) AS `cost`
						FROM `sale_transaction` RIGHT JOIN `sale_transaction_detail`
							ON `sale_transaction`.`id` = `sale_transaction_detail`.`transaction_id`
						WHERE `object_id` = $object_id && `status` = 5
						GROUP BY `sale_transaction_detail`.`transaction_id`");
	$row = mysql_fetch_assoc($res);
	return round($row['cost'], 2 );
}

function GetSumSelfPrice($object_id)
{
	$res = mysql_query("SELECT ROUND(SUM(`sale_transaction_detail`.`self_cost` * `sale_transaction_detail`.`product_quantity`), 2 ) AS `self_cost`
						FROM `sale_transaction` RIGHT JOIN `sale_transaction_detail`
							ON `sale_transaction`.`id` = `sale_transaction_detail`.`transaction_id`
						WHERE `object_id` = $object_id && `status` = 5
						GROUP BY `sale_transaction_detail`.`transaction_id`");
	$row = mysql_fetch_assoc($res);
	return round($row['self_cost'], 2 );
}

function GetCarNumber($overhead_id)
{
	$res = mysql_fetch_assoc(mysql_query("SELECT  `car_number`
										  FROM `overhead`
										  WHERE `id` = $overhead_id"));
	return $res['car_number'];
}

function DeleteOverhead($overhead_id)
{
    mysql_query("DELETE 
				 FROM
	    			`overhead`
				 WHERE
					`id` = $overhead_id");
	
    mysql_query("DELETE 
				 FROM
	    			`overhead_detail`
				 WHERE
					`overhead_id` = $overhead_id");
}

function AddReserve($overhead_id, $object){
	$req = mysql_query("SELECT  `PI`.`production_id`,
								`OI`.`quantity`,
								`OI`.`price`,
								`O`.`confirmation_date`
						FROM `overhead_detail` AS `OD` LEFT JOIN `production_identity` AS `PI`
							ON `OD`.`name` = `PI`.`rs_name` LEFT JOIN `overhead` AS `O`
							ON `OD`.`overhead_id` = `O`.id LEFT JOIN `overhead_identity` AS `OI`
							ON `OD`.`overhead_id` = `OI`.`overhead_id` && `PI`.`id` = `OI`.`identity_id` && `OD`.`id` = `OI`.`overhead_detail_id`
						WHERE `OD`.`overhead_id` = $overhead_id");
	
	while( $res = mysql_fetch_assoc($req)){
		
		$price		= $res['price'];
		$prod_id	= $res['production_id'];
		$vat		= 0;
		$prop		= GetCompanyProperties();
		$pay		= mysql_fetch_assoc(mysql_query("SELECT `partners`.`vat_payer` as `vat`
													 FROM `overhead` LEFT JOIN `partners`
													 	ON `overhead`.`seller_tin` = `partners`.`rs_id`
													 WHERE `overhead`.`id` = $overhead_id"));
		if ($pay['vat'] == 1 && $prop['payer'] == 1) { //Calculate VAT!
			$vat	= $price - ($price / 1.18);
			$price	= $price / 1.18;
		}
		
		$car = mysql_query("SELECT	`PR`.`id`
							FROM	`production` AS `PR`
							WHERE	`PR`.`id` in (SELECT `production_id` FROM `cartridge_identity`) && `PR`.`actived` = 1 && PR.id = $prod_id");
		
		//check production is cartridge
		if (mysql_num_rows($car) == 0){
			mysql_query("INSERT INTO `reserve`
							(`object_id`, `overhead_id`, `production_id`, `quantity`, `price`, `real_price`, `vat`, `create_date`)
						 VALUES
							($object, $overhead_id, $prod_id, $res[quantity], $price, $res[price], $vat, '$res[confirmation_date]')");			
		}else{
			for ($i = 0; $i < $res[quantity]; $i++) {
				mysql_query("INSERT INTO `reserve`
								(`object_id`, `overhead_id`, `production_id`, `quantity`, `price`, `real_price`, `vat`, `create_date`)
							 VALUES
								($object, $overhead_id, $prod_id, 1, $price, $res[price], $vat, '$res[confirmation_date]')");	
			}
		}
		$prod = mysql_query("	SELECT	`price`
								FROM	`reserve`
								WHERE	`production_id` = $prod_id");
		
		if (mysql_num_rows($prod) != 0){
			$price = mysql_fetch_assoc($prod);
			if($price['price'] != $res){				
				$self_price = GetSelfPrice($prod_id);
				
				mysql_query("UPDATE `reserve`
							 SET	`price` = $self_price
							 WHERE	`production_id` = $prod_id" );
				
			}
		}
	}
}

function AddPartnerAcc($overhead_id, $partner_id, $arr)
{
	$c_data = date('Y-m-d H:i:s');
	$type	= $arr[type];
	mysql_query("INSERT INTO `partner_accounts`
					(`transfer_type`,`partner_id`, `overhead_id`, `pay_method`, `payed`, `pay_needed`, `pay_date`, `pay_needed_date`) 
				 VALUES
					($type, '$partner_id', $overhead_id, $arr[method], $arr[payed], $arr[nided], '$c_data', '$arr[needed_date]')");
	
	$credit = 0;
	$debit	= 0;
	
	if($type == 0){
		if($arr[payed] < $arr[nided]){
			$credit = $arr[nided] - $arr[payed]; 
		}elseif ($arr[payed] > $arr[nided]){
			$debit = $arr[payed] - $arr[nided];
		}
	}else{
		if($arr[payed] < $arr[nided]){
			$debit = $arr[nided] - $arr[payed];
		}elseif ($arr[payed] > $arr[nided]){
			$credit = $arr[payed] - $arr[nided];
		}
	}
	
	mysql_query("UPDATE `partners`
				 SET	`money_credit` = `money_credit` + $credit,
				 		`money_debit` = `money_debit` + $debit
				 WHERE	`id` = $partner_id" );
}

function ChackGoodsIdentity($overhead_id) 
{
	$check = true;
	$req = mysql_query("SELECT `PI`.`production_id`
						FROM `overhead_detail` AS `OD` LEFT JOIN `production_identity` AS `PI`
							ON `OD`.`name` = `PI`.`rs_name`
						WHERE `OD`.`overhead_id` = '$overhead_id'");
		
	while( $res = mysql_fetch_assoc($req)){			
		if(empty($res['production_id'])){
			$check = false;
		}			
	}
	return $check;
}

function ChackOverheadIdentity($overhead_id)
{
	$check = false;
	$res1 = mysql_fetch_assoc(mysql_query("SELECT COUNT(*) AS `count`
										   FROM `overhead_detail`
										   WHERE `overhead_id` = $overhead_id"));
	
	$res2 = mysql_fetch_assoc(mysql_query("SELECT COUNT(*) AS `count`
										   FROM `overhead_identity`
										   WHERE `overhead_id` = $overhead_id"));
	if($res1['count'] == $res2['count']){
		$check = true;		
	}
	return $check;
}

function ChackPartnerExist($partner_id)
{
	$check = false;
	$req = mysql_query("SELECT `id`
						FROM `partners`
						WHERE `rs_id` = '$partner_id'");
	
	if (mysql_num_rows($req) == 0){
		$check = true;
	}	
	
	return $check;
}

function SetStatus($overhead_id, $status)
{
	mysql_query("UPDATE
	    			`overhead`
				 SET
				    `status`	= $status
				 WHERE
					`id`		= '$overhead_id'");
}

function GetFullAmount($overhead_id)
{
    $res = mysql_fetch_assoc(mysql_query("SELECT SUM(`amount`) AS `full_amount`
										  FROM `overhead_detail`
										  WHERE `overhead_id` = $overhead_id
										  GROUP BY `overhead_id`"));
	return $res['full_amount'];
}

function GetLocalID()
{
	$result	= mysql_query("SHOW TABLE STATUS LIKE 'overhead'");  
	$row	= mysql_fetch_array($result);
	return $row['Auto_increment'];
}

function GetOverheadType($point)
{
	$data = '';
    $req = mysql_query("SELECT `id`, `name`
						FROM `overhead_types`");
		
	while( $res = mysql_fetch_assoc($req)){			
		if($res['id'] == $point){
			$data .= '<option value="' . $res['id'] . '" selected="selected">' . $res['name'] . '</option>';
		} else {
			$data .= '<option value="' . $res['id'] . '">' . $res['name'] . '</option>';
		}				
	}
		
	return $data;
}

function GetOverheadObject($overhead_id)
{
	$obj = mysql_fetch_assoc(mysql_query("SELECT `object_id`
										  FROM `reserve`
										  WHERE `overhead_id` = $overhead_id
										  GROUP BY `overhead_id`"));
	
	return $obj['object_id'];
}
		
function GetObject($overhead_id, $type)
{
	$data = '';
	if($type == 6){
	    $req = mysql_query("SELECT `id`, `name`
							FROM `object`
							WHERE `type` = 3"); // <<<<---------------------Type!!!
	    
	    if(!empty($overhead_id)){
	    	$par_id = GetParentID($overhead_id);
	    	$obj = mysql_fetch_assoc(mysql_query("SELECT `object_id`
												  FROM `reserve`
												  WHERE `overhead_id` = $par_id
												  GROUP BY `overhead_id`"));
	    }
	}else{
		$req = mysql_query("SELECT `id`, `name`
							FROM `object`
							WHERE `type` = 2 || `type` = 6"); // <<<<---------------------Type!!!
		 
		if(!empty($overhead_id)){
			$obj = mysql_fetch_assoc(mysql_query("SELECT `object_id`
												  FROM `reserve`
												  WHERE `overhead_id` = $overhead_id
												  GROUP BY `overhead_id`"));
		}
	}
		
	while( $res = mysql_fetch_assoc($req)){			
		if($res['id'] == $obj['object_id']){
			$data .= '<option value="' . $res['id'] . '" selected="selected">' . $res['name'] . '</option>';
		} else {
			$data .= '<option value="' . $res['id'] . '">' . $res['name'] . '</option>';
		}				
	}
		
	return $data;
}

function GetAutoNumber($point)
{
	$data = '';
	$req = mysql_query("SELECT `id`, `name`
						FROM `object`
						WHERE `type` = 3"); // <<<<---------------------Type!!!
	$check = false;
	while( $res = mysql_fetch_assoc($req)){
		if($res['id'] == $point){
			$check = true;
			$data .= '<option value="' . $res['id'] . '" selected="selected">' . $res['name'] . '</option>';
		} else {
			$data .= '<option value="' . $res['id'] . '">' . $res['name'] . '</option>';
		}
	}
	
	if(!$check && $point != ''){ // Undefined Auto numbers		
		$data .= '<option value="' . $point . '" selected="selected">' . strtoupper($point) . '</option>';
	}

	return $data;
}

function PayMethod($point)
{
	$data = '';
	$req = mysql_query("SELECT `id`, `name`
							FROM `pay_method`");

	while( $res = mysql_fetch_assoc($req)){
		if($res['id'] == $point){
			$data .= '<option value="' . $res['id'] . '" selected="selected">' . $res['name'] . '</option>';
		} else {
			$data .= '<option value="' . $res['id'] . '">' . $res['name'] . '</option>';
		}
	}

	return $data;
}

function GetBuyer($tin, $name) 
{
	if($tin != ''){
		$res = mysql_query("	SELECT `name` AS `name`,
									   `rs_id` AS `tin`,
									   `legal_address` AS `legal_address`
								FROM `partners`
								WHERE `rs_id` = '$tin'");
	}else{
		$res = mysql_query("	SELECT `name` AS `name`,
									   `rs_id` AS `tin`,
									   `legal_address` AS `legal_address`
								FROM `partners`
								WHERE `name` = '$name'");
	}
	
	if (mysql_num_rows($res) == 0){
		return false;
	}
	$row = mysql_fetch_assoc($res);
	return $row;
}

function GetDriver($tin, $name) 
{
	if($tin != ''){
		$res = mysql_query("	SELECT	`PR`.`name`,
										`PR`.`tin`
								FROM 	`persons` AS `PR` LEFT JOIN `position` AS `PO`
									ON  `PR`.`position` = `PO`.`id`
								WHERE 	`PR`.`tin` = '$tin' && `PR`.`position` = 9 && `PO`.`visible` = 'Yes' ");
	}else{
		$res = mysql_query("	SELECT	`PR`.`name`,
										`PR`.`tin`
								FROM 	`persons` AS `PR` LEFT JOIN `position` AS `PO`
									ON  `PR`.`position` = `PO`.`id`
								WHERE 	`PR`.`name` = '$name' && `PR`.`position` = 9 && `PO`.`visible` = 'Yes' ");
	}
	
	if (mysql_num_rows($res) == 0){
		return false;
	}
	$row = mysql_fetch_assoc($res);
	return $row;
}

function GetCompanyProperties()
{
    $res = mysql_fetch_assoc(mysql_query("SELECT `tin`,
    											 `s_user_id`,
    											 `un_id`,
    											 `name`,
											     `address`,
											     `payer`
									      FROM `company_properties`"));
	return $res;
}

function GetCompanyAccount($overhead_id)
{
    $res = mysql_fetch_assoc(mysql_query("SELECT `pay_method`,
    											 SUM(`payed`) AS `payed`,
    											 `pay_needed_date`
									      FROM `partner_accounts`
										  WHERE `overhead_id` = $overhead_id
										  GROUP BY `overhead_id`"));
	return $res;
}

function GetParentID($overhead_id) 
{
	$res = mysql_fetch_assoc(mysql_query("SELECT `local_par_id`
									      FROM	 `overhead`
									      WHERE  `id` = $overhead_id"));
	return $res['local_par_id'];
}

function GetRSID($overhead_id) 
{
	$res = mysql_fetch_assoc(mysql_query("SELECT `rs_id`
									      FROM	 `overhead`
									      WHERE  `id` = $overhead_id"));
	return $res['rs_id'];
}


function GetPartnerID($partner_id)
{
	$res = mysql_fetch_assoc(mysql_query("SELECT `id`
										  FROM	 `partners`
										  WHERE  `rs_id` = '$partner_id'"));
	return $res['id'];
}

function GetOverhead($overhead_id) 
{
    $res = mysql_fetch_assoc(mysql_query("SELECT `id`,
    											 `stream`,
    											 `rs_id`,
    											 `waybill_number`,
											     `type`,
											     `status`,
											     `buyer_tin`,
											     `buyer_name`,
											     `start_address`,
											     `end_address`,
											     `activate_date`,
											   	 `driver_tin`,
											   	 `driver_name`,
											   	 `car_number`,
											   	 `trans_txt`,
											   	 `transport_coast`,
											   	 `tran_cost_payer`,
											   	 `seller_tin`,
											   	 `seller_name`,
											   	 `is_confirmed`,
											   	 `full_amount`
									      FROM `overhead`
									      WHERE `id` = $overhead_id"));
	return $res;
}

function GetPage($res = '', $o_type = '')
{
    if(empty($o_type)){
    	$o_type = $res['type'];
    }
    
    $stream = $res['stream'];
    
    //-----------Temporary----Only--Distribution------------
    if(empty($o_type)){
    	$o_type = 4;
    }
    //-----------/Temporary---------/Only--/Distribution-------
    
	$data  = '
	<!-- jQuery Dialog -->
    <div id="add-edit-goods-form" class="form-dialog" title="საქონელი">
    	<!-- aJax -->
	</div>
	<div id="dialog-form">
	    <fieldset>
	    	<legend>ძირითადი ინფორმაცია</legend>

	    	<table width="90%" class="dialog-form-table">
				<tr>
					<td style="width: 170px;"><label for="o_type">ზედნადების ტიპი</label></td>
					<td style="width: 170px;"><label for="o_number">ზედნადების №</label></td>
					<td style="width: 170px;"><label for="actation_data">გააქტიურების თარიღი</label></td>
					<td style="width: 170px;"><label for="object" class="object">საწყობი</label></td>
				</tr>
				<tr>
					<td>
						<select id="o_type" class="idls">' . GetOverheadType($o_type) . '</select>
					</td>
					<td>
						<input type="text" id="o_number" class="idle" disabled="disabled" onblur="this.className=\'idle\'" onfocus="this.className=\'activeField\'" value="' . $res['waybill_number'] . '" />
					</td>
					<td>
						<input type="text" id="actation_data" class="idle" disabled="disabled" onblur="this.className=\'idle\'" onfocus="this.className=\'activeField\'" value="' . $res['activate_date'] . '" />
					</td>
					<td>
						<select id="object" class="idls object">' . GetObject($res['id'], $o_type) . '</select>
					</td>
				</tr>
			</table>
        </fieldset>
    ';
	
    $data .= GetRelationsSection($res, $o_type, $stream);
    $data .= GetTransportSection($res, $o_type);
    $data .= GetAccountSection($res, $o_type);
    
    $data .= '
		<fieldset>
	    	<legend>საქონელი</legend>

		    <div class="inner-table">
			    <div id="dt_example" class="ex_highlight_row">
			        <div id="container" class="overhead_container">
			        	<div id="button_area">
			        		<button id="add_button_goods">დამატება</button><button id="delete_button_goods">წაშლა</button>
			        	</div>
			            <div id="dynamic">';
	$data .= GetTableSection($o_type, $stream);
	$data .= '
			            </div>
			            <div id="bottom_button_area">
			        		<button id="sub_overhead_button" class="left">ახალი ქვე-ზედნადები</button>
			        	</div>
			        </div>
			    </div>
			</div>
		</fieldset>
		<!-- ID -->
		<input type="hidden" id="overhead_id" value="' . $res['id'] . '" />
		<input type="hidden" id="local_overhead_id"/>
		<input type="hidden" id="overhead_status" value="' . $res['status'] . '" />
		<input type="hidden" id="overhead_confirmed" value="' . $res['is_confirmed'] . '" />
    </div>    
    ';
    
	return $data;
}

function GetRelationsSection($res = '', $type, $stream)
{
	$prop = GetCompanyProperties();
	if($stream == 0){
		$data = '        
			<fieldset>
				<legend>გამყიდველი (გამგზავნი)</legend>
			
				<table width="95%" class="dialog-form-table">
					<tr>
						<td style="width: 200px;"><label for="seller_i_number">საიდენტ. ნომერი</label></td>
						<td style="width: 300px;"><label for="seller_name">დასახელება / სახელი, გვარი</label></td>
						<td style="width: 350px;"><label for="seller_addree">ტრანსპორტირების დაწყების ადგილი (მისამართი)</label></td>
					</tr>
					<tr>
						<td>
							<input type="text" id="seller_i_number" class="idle" disabled="disabled" onblur="this.className=\'idle\'" onfocus="this.className=\'activeField\'" value="' . $prop['tin'] . '" />
						</td>
						<td>
							<input type="text" id="seller_name" class="idle address" disabled="disabled" onblur="this.className=\'idle address\'" onfocus="this.className=\'activeField address\'" value="' . $prop['name'] . '" />
						</td>
						<td>
							<input type="text" id="seller_addree" class="idle large" disabled="disabled" onblur="this.className=\'idle large\'" onfocus="this.className=\'activeField large\'" value="' . $prop['address'] . '" />
						</td>
					</tr>
				</table>
			</fieldset>
		';
	}else{
		$data = '        
			<fieldset>
				<legend>გამყიდველი (გამგზავნი)</legend>
			
				<table width="95%" class="dialog-form-table">
					<tr>
						<td style="width: 200px;"><label for="seller_i_number">საიდენტ. ნომერი</label></td>
						<td style="width: 300px;"><label for="seller_name">დასახელება / სახელი, გვარი</label></td>
						<td style="width: 350px;"><label for="seller_addree">ტრანსპორტირების დაწყების ადგილი (მისამართი)</label></td>
					</tr>
					<tr>
						<td>
							<input type="text" id="seller_i_number" class="idle" disabled="disabled" onblur="this.className=\'idle\'" onfocus="this.className=\'activeField\'" value="' . $res['seller_tin'] . '" />
						</td>
						<td>
							<input type="text" id="seller_name" class="idle address" disabled="disabled" onblur="this.className=\'idle address\'" onfocus="this.className=\'activeField address\'" value="' . $res['seller_name'] . '" />
						</td>
						<td>
							<input type="text" id="seller_addree" class="idle large" disabled="disabled" onblur="this.className=\'idle large\'" onfocus="this.className=\'activeField large\'" value="' . $res['start_address'] . '" />
						</td>
					</tr>
				</table>
			</fieldset>
		';
	}

	if($type != 1 && $type != 4){ //1 is Domestic Traffic, 4 is Distribution
		$data .= '        
			<fieldset>
				<legend>მყიდველი (მიმღები)</legend>
			
				<table width="95%" class="dialog-form-table">
					<tr>
						<td style="width: 200px;"><label for="buyer_i_number">საიდენტ. ნომერი</label></td>
						<td style="width: 300px;"><label for="buyer_name">დასახელება / სახელი, გვარი</label></td>
						<td style="width: 350px;"><label for="buyer_addree">ტრანსპორტირების დაწყების ადგილი (მისამართი)</label></td>
					</tr>
					<tr>
						<td>
							<input type="text" id="buyer_i_number" class="idle" onblur="this.className=\'idle\'" onfocus="this.className=\'activeField\'" value="' . $res['buyer_tin'] . '" />
						</td>
						<td>
							<div class="seoy-row" id="buyer_name_seoy">
								<input type="text" id="buyer_name" class="idle seoy-address" onblur="this.className=\'idle seoy-address\'" onfocus="this.className=\'activeField seoy-address\'" value="' . $res['buyer_name'] . '" />
								<button id="buyer_name_btn" class="combobox">buyer_name</button>
							</div>
						</td>
						<td>
							<input type="text" id="buyer_addree" class="idle large" onblur="this.className=\'idle large\'" onfocus="this.className=\'activeField large\'" value="' . $res['end_address'] . '" />
						</td>
					</tr>
				</table>
			</fieldset>
		';
	}
	
	return $data;
}

function GetTransportSection($res = '', $type)
{
	if($type != 3 && $type != 6){//3 is Without Transportation, 6 is Sub Overhead
		$data = '        
			<fieldset>
		    	<legend>ტრანსპორტირება</legend>
				
				<table width="100%" class="dialog-form-table">
					<tr>
						<td style="width: 170px;"><label for="transport_type">ტრანსპორტირების სახე</label></td>
					</tr>
					<tr>
						<td>
							<select id="transport_type" class="idls" disabled="disabled">
								<option value="0">აირჩიეთ</option>
								<option value="1" selected="selected">საავტომობილო</option>
								<option value="2">სარკინიგზო</option>
								<option value="3">საავიაციო</option>
								<option value="4">სხვა</option>
								<option value="6">საავტომობილო - უცხო ქვეყნის</option>
							</select>
						</td>
					</tr>
				</table>
				
				<table width="96%" class="dialog-form-table">
					<tr>
						<td style="width: 120px;"><label for="driver_id">მძღოლის პ/ნ</label></td>
						<td style="width: 275px;"><label for="driver_name">მძღოლის სახელი და გვარი</label></td>
						<td style="width: 120px;"><label for="auto_number">ა/მ ნომერი</label></td>
						<td style="width: 120px;"><label for="auto_trailer">მისაბმელი</label></td>
						<td style="width: 170px;" colspan="2"><label>ტრანსპორტირების ხარჯი</label></td>
					</tr>
					<tr>
						<td>
							<input type="text" id="driver_id" class="idle user_id" onblur="this.className=\'idle user_id\'" onfocus="this.className=\'activeField user_id\'" value="' . $res['driver_tin'] . '" />
						</td>
						<td>
							<div class="seoy-row" id="driver_name_seoy">
								<input type="text" id="driver_name" class="idle address" onblur="this.className=\'idle address\'" onfocus="this.className=\'activeField address\'" value="' . $res['driver_name'] . '" />
								<button id="driver_name_btn" class="combobox">driver_name</button>
							</div>
						</td>
						<td>
							<select id="auto_number" class="idls user_id">' . GetAutoNumber($res['car_number']) . '</select>
						</td>
						<td>
							<input type="text" id="auto_trailer" class="idle user_id" onblur="this.className=\'idle user_id\'" onfocus="this.className=\'activeField user_id\'" value="' . $res['trans_txt'] . '" />
						</td>
						<td>
							<input type="text" id="auto_expence_no" class="idle small" onblur="this.className=\'idle small\'" onfocus="this.className=\'activeField small\'" value="' . $res['transport_coast'] . '" />
						</td>
						<td>
							<select id="auto_expence" class="idls num">
								<option value="1">მყიდველი</option>
								<option value="2">გამყიდველი</option>
							</select>
						</td>
					</tr>
				</table>
	        </fieldset>
		';
	}
	
	return $data;
}

function GetTableSection($type, $stream)
{
	if($stream == 0){	
		$data = '
							<table class="display" id="goods_list">
			                    <thead>
			                        <tr id="datatable_header">				                        
			                            <th>ID</th>
			                            <th style="width: 40px">№</th>
			                            <th class="min">საქონლის კოდი</th>
			                            <th style="width: ';
		if($type != 6){ //If sub Overhead
			$data .= '279px';
		}else{
			$data .= '234px';
		}
		$data .= '">საქონლის დასახელება</th>
			                            <th style="width: 100px">ზომის<br />ერთეული</th>
			                            <th style="width: 80px">რაოდ.</th>
			                            <th style="width: 80px">ერთ.<br />ფასი</th>
			                            <th style="width: 105px">საქონლის<br />ფასი</th>';
		if($type != 6){ //If sub Overhead
			$data .= '<th class="check">#</th>';
		}else{
			$data .= '<th style="width: 80px">ნაშთი<br />რაოდ.</th>';
		}
		$data .= '
			                        </tr>
			                    </thead>
			                    <thead>
			                        <tr class="search_header">				                        
			                            <th class="colum_hidden">
			                            	<input type="text" name="search_id" value="ფილტრი" class="search_init" />
			                            </th>
			                            <th>
			                            	<input type="text" name="search_number" value="ფილტრი" class="search_init hidden-input" />
			                            </th>
			                            <th>
			                                <input type="text" name="search_code" value="ფილტრი" class="search_init" />
			                            </th>
			                            <th>
			                                <input type="text" name="search_name" value="ფილტრი" class="search_init" />
			                            </th>
			                            <th></th>
			                            <th></th>
			                            <th></th>
			                            <th></th>
										<th>';
		if($type != 6){ //If sub Overhead
			$data .= '<input type="checkbox" name="check-all" id="check-all-goods">';
		}
		$data .= '
										</th>
			                        </tr>
			                    </thead>
			                </table>';
	}else{	
		$data = '
							<table class="display" id="goods_list">
			                    <thead>
			                        <tr id="datatable_header">				                        
			                            <th>ID</th>
			                            <th style="width: 40px">№</th>
			                            <th style="width: 50%">დასახელება ზედნადებით</th>
										<th style="width: 50%">საქონლის დასახელება</th>
			                            <th style="width: 80px">რაოდ.</th>
			                            <th style="width: 80px">ერთ.<br />ფასი</th>
			                            <th style="width: 105px">საქონლის<br />ფასი</th>
			                        </tr>
			                    </thead>
			                    <thead>
			                        <tr class="search_header">				                        
			                            <th class="colum_hidden">
			                            	<input type="text"  name="search_id" value="ფილტრი" class="search_init" />
			                            </th>
			                            <th>
			                            	<input type="text" name="search_number" value="ფილტრი" class="search_init hidden-input" />
			                            </th>
			                            <th>
			                                <input type="text" name="search_name" value="ფილტრი" class="search_init" />
			                            </th>
			                            <th>
			                            	<input type="text" name="search_production_name" value="ფილტრი" class="search_init" />
			                            </th>
			                            <th></th>
			                            <th></th>
										<th></th>
			                        </tr>
			                    </thead>
			                </table>';
	}

	return $data;
}

function GetAccountSection($res = '', $type)
{    
    if($type != 1){ //1 is Domestic Traffic
    	if (!empty($res['id'])) {
    		$account = GetCompanyAccount($res['id']);    	
		    $data .= '
				<fieldset>
					<legend>ანგარიშწორება</legend>
			
					<table width="90%" class="dialog-form-table">
						<tr>
							<td style="width: 200px;"><label for="pay_method">ანგარიშსწორების ფორმა</label></td>
							<td style="width: 150px;"><label for="pay_nided">სულ გადასახდელი</label></td>
							<td style="width: 150px;"><label for="payed">გადახდილი</label></td>
							<td style="width: 300px;"><label for="pay_needed_date">დავალიანების გადახდის თარიღი</label></td>
						</tr>
						<tr>
							<td>
								<select id="pay_method" class="idls">' . PayMethod($account['pay_method']) . '</select>
							</td>
							<td>
								<input type="text" id="pay_nided" class="idle price" disabled="disabled" onblur="this.className=\'idle price\'" onfocus="this.className=\'activeField price\'" value="' . $res['full_amount'] . '" />
							</td>
							<td>
								<input type="text" id="payed" class="idle price" onblur="this.className=\'idle price\'" onfocus="this.className=\'activeField price\'" value="' . $account['payed'] . '" />
							</td>
							<td>
								<input type="text" id="pay_needed_date" class="idle" onblur="this.className=\'idle\'" onfocus="this.className=\'activeField\'" value="' . $account['pay_needed_date'] . '" />
							</td>
						</tr>
					</table>
				</fieldset>
				';
    	}
    }

	return $data;
}

?>